package com.wuye.repository;


import com.wuye.model.option.QuestionAnswer;
import com.wuye.model.option.UserAnswers;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import java.util.List;

/**
 * Created by jiawenlong on 2016/8/1.
 */
public interface QuestionAnswerRepository extends PagingAndSortingRepository<QuestionAnswer, Long> {

    List<QuestionAnswer> findByQuestionnaireId(long questionnaireId);

    List<QuestionAnswer> findByUserAnswerId(long userAnswerId);

    List<QuestionAnswer> findByUserIdAndUserAnswerId(String userId, long userAnswerId);

    @Query(nativeQuery = true, value = "select g.name,q.title,num.*  from ( SELECT contT.id as id , " +
            " sum(CASE contT.answer when 0 then  contT.cont else 0 end)as answer1 , " +
            " sum(CASE contT.answer when 1 then  contT.cont else 0 end)as answer2 , " +
            " sum(CASE contT.answer when 2 then  contT.cont else 0 end)as answer3 ," +
            " group_concat(contT.mark separator ';') as mark " +
            " from ( " +
            " select count(*) as cont,answer,mark,c.id from QuestionAnswer,(SELECT  questionId as id from QuestionAnswer  where questionnaireId=?1) c  " +
            " where questionId=c.id and questionnaireId=?1 GROUP BY answer,c.id,mark ORDER BY c.id) contT  " +
            " GROUP BY contT.id) num LEFT JOIN Question q on num.id = q.id LEFT JOIN QuestionGroup g on q.groupId = g.id ORDER BY g.id asc ,q.id asc")
    List<Object[]> queryExportData(long questionnaireId);

}